package com.softace.jdbc;

/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
import com.mysql.jdbc.ResultSetMetaData;
import java.io.File;
import java.io.FileWriter;
import java.io.IOException;
import java.io.Writer;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.LinkedHashMap;
import java.util.Map;
import java.util.Set;

/**
 * This is the Database utility class. This class should be extended by your
 * Class to use the functionality of {@code MySqlJdbcUtilities}. This class do
 * the following tasks:<br>
 * <ol><li>Gets the Connection from DBConnection Class</li>
 * <li>Provides the querying tools to its child classes</li></ol>
 * This class can also be used to get the meta data of the database.<br>i.e: the
 * column names from a {@code ResultSet} etc
 *
 * @author Sarfraz Ahmad
 * @author S1LENT W@RRIOR
 * @version 2.0
 *
 */
public class JdbcManager {

    /**
     * The MySqlJdbcUtilities object. <br>This object acts as an interface b/w
     * the underlying Database and the layers above this Class
     */
    public MySqlJdbcUtilities jdbcUtilities;

    /**
     * Gets the connection object
     *
     * @return The connection to the database
     */
    public Connection getConnection() {
        return jdbcUtilities.getConnection();
    }

    /**
     * The Constructor for JdbcUtilities class Instantiates some protected and
     * private fields of this class.
     *
     * @param jdbcParameters the connection parameters
     */
    public JdbcManager(IConnectionParameters jdbcParameters) {
        jdbcUtilities = new MySqlJdbcUtilities(jdbcParameters);
    }

    /**
     * Gets the {@code JdbcResultSetMetaData} for the given {@code ResultSet}.
     * {@code JdbcResultSetMetaData} contains some necessary parameters of a
     * result set like the column count, row count, and column columnNames
     * etc.<br>Note: This method does not close the resultSet. The caller of
     * this method is responsible for closing the ResultSet. This method also
     * calls the {@code beforeFirst()} method of the {@code ResultSet}.
     *
     * @param resultSet The resultSet object from which the meta data is to be
     * extracted
     * @return the description of this ResultSet object's columns
     * @throws java.sql.SQLException if a database access error occurs or this
     * method is called on a closed result set
     * @see JdbcResultSetMetaData
     * @see ResultSet#beforeFirst()
     *
     */
    public static final JdbcResultSetMetaData getMetaDataForResultSet(ResultSet resultSet) throws SQLException {
        int cursorPosition = resultSet.getRow();
        ResultSetMetaData metaData = (com.mysql.jdbc.ResultSetMetaData) resultSet.getMetaData();
        int columnCount = metaData.getColumnCount();
        Map<String, JdbcColumn> columns = new LinkedHashMap();
        for (int i = 1; i <= columnCount; i++) {
            String columnTitle = String.format("%s.%s", metaData.getTableAlias(i),
                    metaData.getColumnLabel(i));
            if (metaData.getTableAlias(i).isEmpty()) {
                columnTitle = metaData.getColumnLabel(i);
            }
            columns.put(columnTitle, new JdbcColumn(i, metaData.getColumnName(i),
                    metaData.getColumnLabel(i), columnTitle, metaData.getColumnType(i),
                    metaData.getColumnClassName(i), metaData.getTableName(i),
                    metaData.getTableAlias(i)));
        }

        resultSet.last();
        int rowCount = resultSet.getRow();
        if (cursorPosition == 0) {
            resultSet.beforeFirst();
        } else {
            resultSet.absolute(cursorPosition);
        }
        return new JdbcResultSetMetaData(columnCount, rowCount, columns);
    }

    /**
     * Gets a CSV-format String from the given ResultSet. <br>Note: This method
     * does not close the resultSet. The caller of this method is responsible
     * for closing the ResultSet.
     *
     * @param resultSet The ResultSet object from which CSV formatted String is
     * to be returned
     * @param separator The column or field separator. Usually a semi-colon (;)
     * or comma (,) is used as a separator
     * @param includeColumnNames A flag indicating whether column names should
     * be included in the output String or not
     * @return The data from ResultSet in a CSV-format
     * @throws SQLException If the ResultSet is already closed or null
     */
    public static final String toCsvString(ResultSet resultSet, char separator, boolean includeColumnNames)
            throws SQLException {
        if (resultSet == null || resultSet.isClosed()) {
            throw new SQLException();
        }
        JdbcResultSetMetaData metaData = JdbcManager.getMetaDataForResultSet(resultSet);
        int _columnCount = metaData.getColumnCount();
        String _columnNames;
        String lineSeperator = System.getProperty("line.separator");
        StringBuilder sb = new StringBuilder();
        if (includeColumnNames) {
            Set<String> cols = metaData.getColumns().keySet();
            _columnNames = cols.toString().replaceAll("\\[", "").replaceAll("\\]", "").replaceAll(" ", "");
            sb = new StringBuilder(_columnNames);
            sb.append(lineSeperator);
        }
        while (resultSet.next()) {
            for (int i = 1; i <= _columnCount; i++) {
                sb.append(resultSet.getString(i));
                if (i != _columnCount) {
                    sb.append(separator);
                }
            }
            sb.append(lineSeperator);
        }
        return sb.toString();
    }

    /**
     * Saves the ResultSet to a file in a CSV format. <br>Note: This method does
     * not close the resultSet. The caller of this method is responsible for
     * closing the ResultSet.
     *
     * @param resultSet The ResultSet object from which CSV formatted String is
     * to be returned
     * @param separator The column or field separator. Usually a semi-colon (;)
     * or comma (,) is used as a separator
     * @param includeColumnNames A flag indicating whether column names should
     * be included in the output String or not
     * @param filePath The destination path (including the file name and file
     * type) where file has to written
     * @return The newly created CSV File
     * @throws SQLException If the ResultSet is already closed or null
     * @throws IOException If an I/O error occurs
     * @see #toCsvString(java.sql.ResultSet, char, boolean)
     */
    public static final File toCsvFile(ResultSet resultSet, char separator, boolean includeColumnNames, String filePath)
            throws SQLException, IOException {
        String csvString = JdbcManager.toCsvString(resultSet, separator, includeColumnNames);
        File file = new File(filePath);
        // write to file
        Writer writer = new FileWriter(file);
        try {
            writer.write(csvString);
        } finally {
            writer.flush();
            writer.close();
        }
        return file;
    }

    /**
     * Saves the ResultSet to a file in a CSV format. This overloaded function
     * takes the file object instead of the filePath.<br>Note: This method does
     * not close the resultSet. The caller of this method is responsible for
     * closing the ResultSet.
     *
     * @param resultSet The ResultSet object from which CSV formatted String is
     * to be returned
     * @param separator The column or field separator. Usually a semi-colon (;)
     * or comma (,) is used as a separator
     * @param includeColumnNames A flag indicating whether column names should
     * be included in the output String or not
     * @param file The file in which CSV String has to be written
     * @return The CSV File
     * @throws SQLException If the ResultSet is already closed or null
     * @throws IOException If an I/O error occurs
     * @see #toCsvString(java.sql.ResultSet, char, boolean)
     */
    public static final File toCsvFile(ResultSet resultSet, char separator, boolean includeColumnNames, File file)
            throws SQLException, IOException {
        String csvString = JdbcManager.toCsvString(resultSet, separator, includeColumnNames);
        // write to file
        Writer writer = new FileWriter(file);
        try {
            writer.write(csvString);
        } finally {
            writer.flush();
            writer.close();
        }
        return file;
    }

    /**
     * Disconnects from the database
     *
     * @param resultSet the {@code ResultSet} object or null
     * @param statement the {@code Statement} object or null
     */
    public void disconnect(ResultSet resultSet, Statement statement) {
        this.jdbcUtilities.disconnect(resultSet, statement);
    }

}
